package com.seari.custody.component;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.resource.ClassPathResource;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.http.HttpUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.RowUtil;
import cn.hutool.poi.excel.cell.CellUtil;
import cn.hutool.poi.word.DocUtil;
import com.google.common.base.Splitter;
import com.google.common.base.Strings;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.seari.custody.bean.*;
import com.seari.custody.enums.MeetingFileType;
import com.seari.custody.enums.ProjectLevel;
import com.seari.custody.exception.BIZException;
import com.seari.custody.pojo.CellRangeAddressExcelVo;
import com.seari.custody.service.MeetingFileService;
import com.seari.custody.service.OutLineProjectService;
import com.seari.custody.service.ProjectService;
import com.seari.custody.util.Common;
import com.seari.custody.util.POIUtil;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xwpf.usermodel.*;
import org.apache.xmlbeans.XmlCursor;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTPPr;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTSpacing;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STLineSpacingRule;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.core.io.InputStreamSource;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.*;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.*;

import static cn.hutool.core.util.NumberUtil.*;
//projectid ->project file  like file_type order by file_ecm_id max(one)

/**
 * 监护大纲 等级计算
 */
@Component
@Slf4j
public class OutlineProjectCalc {
    @Autowired
    private ProjectService projectService;
    @Autowired
    private MinIoUtil minIoUtil;
    @Autowired
    private MeetingFileService meetingFileService;
    @Autowired
    private OutLineProjectService outLineProjectService;

    final private static String PICK = "√";
    final private static String UN_PICK = "□";

    //监护类型 1 基坑 其余非基坑
    final private static String subwayStructureSide_A = "位于盾构、矩形段、地面线侧方";
    final private static String subwayStructureSide_B = "位于车站结构侧方";
    final private static String subwayStructureSide_C = "位于高架侧方 ";
    final private static String subwayStructureSide_D = "位于地铁结构上方";

    //非基坑
    final private String DGCY = "盾构穿越";
    final private String FKW = "管线-非开挖";
    final private String MW = "管线-明挖";
    final private String JX = "加卸";
    final private String XZ = "卸载";
    final private String ZJGC = "桩基工程";
    final private String QTGC = "其他工程";

    /**
     * 生成技术要求表
     *
     * @param outlineProjectData
     * @return
     */
    public OutlineProject getOutlineProject(OutlineProjectData outlineProjectData) {
        String level = outlineProjectData.getLevel();
        OutlineProject outlineProject = new OutlineProject();
        String projectType = outlineProjectData.getProjectType();
        //1 基坑工程
        //2 穿越工程
        //3加卸载、管线、桥梁、桩基及其他
        outlineProject.setProjectType(outlineProjectData.getProjectType());
        //监护等级： T 特级1 一级2 二级3 三级
        outlineProject.setLevel(level);
        //车站 ：0无（默认）1地上 2 地下
        outlineProject.setLineType(outlineProjectData.getLineType());
        //车站 ：0无（默认）1地上 2 地下
        outlineProject.setStationFlag(outlineProjectData.getStationFlag());
        //区间：0无（默认）1单圆2双圆3 矩形4 高架5 地面
        outlineProject.setStationArea(outlineProjectData.getStationArea());
        //覆土深度(m)
        outlineProject.setHeight(outlineProjectData.getF());

        outlineProject.setProjectId(outlineProjectData.getProjectId());
        //车站-垂直位移：0无（默认）1 自动 2 人工
        outlineProject.setFastenMonitor("true");
        if ("1".equalsIgnoreCase(outlineProjectData.getProjectType())) {
            outlineProject.setDitchMeasure("true");
        }
        if (outlineProjectData.getGreenProject() == 1) {
            outlineProject.setGreenMeasure("true");
        }
        outlineProject.setSidelineFlag("true");
        outlineProject.setSidelineDataFlag("true");

        outlineProject.setSidelineFlag("true");
        switch (outlineProject.getLevel()) {
            case "1":
                outlineProject.setStationCzwy(3);
                outlineProject.setStationMain(3);
                outlineProject.setDgCzwy(3);
                outlineProject.setDgSpwy(2);
                outlineProject.setDgSlbx(3);
                outlineProject.setJxdCzwy(3);
                outlineProject.setJxdSpwy(2);
                outlineProject.setGjDccj(3);
                outlineProject.setGjLzcj(3);
                outlineProject.setGjLzqx(3);
                outlineProject.setGjLzSpwy(3);
                outlineProject.setDmxCzwy(3);
                outlineProject.setDmxSpwy(2);
                outlineProject.setSoilMonitor("true");
                outlineProject.setThirdpartyMonitor("true");
                outlineProject.setRemoteMonitor("true");
                switch (projectType) {
                    case "1":
                        outlineProject.setDitchCheck("true");
                        break;
                    case "2":
                        outlineProject.setAcrossCheck("true");
                        break;
                    case "3":
                        outlineProject.setPileCheck("true");
                        break;
                }


            case "2":
                outlineProject.setStationCzwy(3);
                outlineProject.setStationMain(3);
                outlineProject.setDgCzwy(3);
                outlineProject.setDgSpwy(2);
                outlineProject.setDgSlbx(3);
                outlineProject.setJxdCzwy(3);
                outlineProject.setJxdSpwy(2);
                outlineProject.setGjDccj(2);
                outlineProject.setGjLzcj(2);
                outlineProject.setGjLzqx(2);
                outlineProject.setGjLzSpwy(2);
                outlineProject.setDgCzwy(3);
                outlineProject.setDmxSpwy(2);
                switch (projectType) {
                    case "1":
                        outlineProject.setDitchCheck("true");
                        break;
                    case "2":
                        outlineProject.setAcrossCheck("true");
                        break;
                    case "3":
                        outlineProject.setPileCheck("true");
                        break;
                }
                outlineProject.setDitchMeasure("true");
                break;
            case "3":
                outlineProject.setStationCzwy(2);
                outlineProject.setStationMain(2);
                outlineProject.setDgCzwy(3);
                outlineProject.setDgSlbx(2);
                outlineProject.setJxdCzwy(2);
                outlineProject.setGjDccj(2);
                outlineProject.setGjLzcj(2);
                outlineProject.setGjLzqx(2);
                outlineProject.setDmxCzwy(2);
            case "4":
                outlineProject.setStationCzwy(2);
                outlineProject.setStationMain(2);
                outlineProject.setDgCzwy(2);
                outlineProject.setDgSlbx(2);
                outlineProject.setJxdCzwy(2);
                outlineProject.setGjDccj(2);
                outlineProject.setGjLzcj(2);
                outlineProject.setGjLzqx(2);
                outlineProject.setDgCzwy(2);
                break;
        }
        outlineProject.setStationSub(2);

        return outlineProject;

    }


    /**
     * 计算监护等级
     *
     * @return
     */
    public Integer calculateLevel(OutlineProjectData projectData) {

        ProjectLevel level = null;

        Double h = projectData.getH();
        Double dh = projectData.getDHorizontal();
        Double f = projectData.getF();
        Double s = projectData.getS();
        String subType = projectData.getProjectSubType().trim();


        //基坑类型
        if ("1".equalsIgnoreCase(projectData.getProjectType())) {
            switch (subType) {
                case subwayStructureSide_A:
                    boolean branchOne = (h >= 15) && (dh <= h);
                    boolean branchTwo = (h >= 10 & h < 15) && (dh <= h) && (f <= h);
                    boolean branchThree = (h >= 10 & h < 15) && (dh <= h) && (f <= h) && (s >= 10000);
                    if (branchOne || branchTwo || branchThree) {
                        level = ProjectLevel.T;
                    } else {
                        branchOne = (h >= 15) && ((dh > h) && (2 * h >= dh));
                        branchTwo = (((h >= 10) && (15 > h)) && (dh <= h) && (f > h));
                        branchThree = (((h >= 5) && (10 > h)) && (dh <= h) && (f > h) && (s >= 10000));
                        boolean branchFour = (((h >= 5) && (10 > h)) && (dh <= h) && (f <= h) && ((5000 <= s) && (s < 10000)));
                        if (branchOne || branchTwo || branchThree || branchFour) {
                            level = ProjectLevel.ONE;
                        } else {
                            branchOne = (h >= 15) && (dh > 2 * h);
                            branchTwo = ((h >= 10) && (15 > h)) && ((h < dh) && (2 * h > dh));
                            branchThree = (((h >= 5) && (10 > h)) && ((dh > 2 * h) && (15 > dh)));
                            branchFour = ((dh <= 3 * h) && (h <= 5));
                            boolean branchFive = ((dh < 2 * h) && ((s >= 3000) && (10000 > s)));
                            if (branchOne || branchTwo || branchThree || branchFour || branchFive) {
                                level = ProjectLevel.TWO;
                            } else {
                                level = ProjectLevel.THREE;
                            }
                        }
                    }
                    break;
                case subwayStructureSide_B:
                    branchOne = (h >= 20) && (dh <= (h - 10)) && (s > 10000);
                    branchTwo = (h >= 10) && (dh < 10) && (s > 10000);
                    if (branchOne || branchTwo) {
                        level = ProjectLevel.T;
                    } else {
                        branchOne = (h >= 5) && (dh <= h) && (s > 5000);
                        if (branchOne) {
                            level = ProjectLevel.ONE;
                        } else {
                            branchOne = (h > 5) && (dh <= 2 * h) && (s > 5000);
                            if (branchOne) {
                                level = ProjectLevel.TWO;
                            } else {
                                level = ProjectLevel.THREE;
                            }
                        }
                    }
                    break;
                case subwayStructureSide_C:
                    branchOne = (h >= 20) && (dh <= (h - 10)) && (s > 5000);
                    branchTwo = (h >= 10) && (dh <= 5) && (s > 5000);
                    if (branchOne || branchTwo) {
                        level = ProjectLevel.T;
                    } else {
                        branchOne = (h >= 15) && (dh <= (h - 5)) && (s > 5000);
                        branchTwo = (h >= 15) && (dh <= 10) && (s > 5000);
                        if (branchOne || branchTwo) {
                            level = ProjectLevel.ONE;
                        } else {
                            branchOne = (h >= 6) && (dh <= 2 * h) && (s > 3000);
                            if (branchOne) {
                                level = ProjectLevel.TWO;
                            } else {
                                level = ProjectLevel.THREE;
                            }
                        }
                    }
                    break;
                case subwayStructureSide_D:
                    Double aboveCoverWidth = projectData.getAboveCoverWidth();

                    branchOne = h >= (f / 2);
                    if (branchOne) {
                        level = ProjectLevel.T;

                    } else {

                        branchOne = (h >= (f / 3)) && aboveCoverWidth > 2;
                        if (branchOne) {
                            level = ProjectLevel.ONE;

                        } else {
                            branchOne = (h >= (f / 6)) && aboveCoverWidth > 3;
                            if (branchOne) {
                                level = ProjectLevel.TWO;

                            } else {
                                level = ProjectLevel.THREE;
                            }
                        }
                    }
                    break;
                default:
                    level = ProjectLevel.THREE;
                    break;

            }
        } else {
            //非基坑类型
            Double dv = projectData.getDVertical();
            Integer isUnderCross = projectData.getIsUnderCross();
            Double dH = projectData.getDBridge();
            Double o = projectData.getDiameter();
            Double dvDividedO = null;
            if (o != null && dv != null) {
                dvDividedO = dv / o;
            }
            Boolean aboveDig = projectData.getAboveDig();
            Double elevationChanges = projectData.getElevationChanges();
            Boolean shadowBridge10 = projectData.getShadowBridge10();
            Double pileDisFromSubWayStr = projectData.getPileDisFromSubWayStr();

            switch (subType) {
                case DGCY:
                    boolean branch1 = (isUnderCross == 2) && (dvDividedO <= 1);
                    boolean branch2 = (dvDividedO <= 0.5) && (isUnderCross == 1);
                    boolean branch3 = dH <= 3;

                    if (branch1 || branch2 || branch3) {
                        level = ProjectLevel.T;
                    } else {
                        branch1 = ((dvDividedO > 1) && (dvDividedO <= 2)) && (isUnderCross == 2);
                        branch2 = ((dvDividedO > 0.5) && (dvDividedO <= 1)) && (isUnderCross == 1);
                        branch3 = ((dH > 3) && (dH <= o));
                        if (branch1 || branch2 || branch3) {
                            level = ProjectLevel.ONE;
                        } else {
                            level = ProjectLevel.TWO;
//                            branch1 = (dvDividedO > 2) && isUnderCross;
//                            branch2 = (dvDividedO > 1) && !isUnderCross;
//                            if (branch1 || branch2) {
//                                level = ProjectLevel.TWO;
//                            }
                        }
                    }
                    break;
                case FKW:
                    branch1 = (dvDividedO <= 1) && (isUnderCross == 2);
                    branch2 = dH <= o;
                    if (branch1 || branch2) {
                        level = ProjectLevel.T;
                    } else {
                        branch1 = (dvDividedO > 1) && (dvDividedO <= 2) && (isUnderCross == 2);
                        branch2 = (dvDividedO <= 1) && (isUnderCross == 1);
                        branch3 = (dH > o) && (3 * o >= dH);
                        if (branch1 || branch2 || branch3) {
                            level = ProjectLevel.ONE;
                        } else {
                            branch1 = (dvDividedO > 2) && (isUnderCross == 2);
                            branch2 = (dvDividedO > 1) && (dvDividedO <= 3) && (isUnderCross == 1);
                            branch3 = (dH > 3 * o) && (5 * o >= dH);
                            if (branch1 || branch2 || branch3) {
                                level = ProjectLevel.TWO;
                            } else {
                                level = ProjectLevel.THREE;
                            }
                        }
                    }
                    break;
                case MW:
                    branch1 = (h >= 5) && (dh <= 2 * h);
                    branch2 = ((h >= 3) && (h < 5)) && (dv <= 2 * h) && dh <= 2 * h;
                    if (branch1 || branch2) {
                        level = ProjectLevel.ONE;
                    } else {
                        branch1 = (h >= 5) && ((dh > 2 * h) && (dh <= 15));
                        branch2 = (5 > h) && (h >= 3) && (dv >= 2 * h) && (dh <= h);
                        branch3 = (dh <= 3 * h) && (dv <= 2 * h) && aboveDig;
                        if (branch1 || branch2 || branch3) {
                            level = ProjectLevel.TWO;
                        } else {
                            level = ProjectLevel.THREE;
                        }
                    }
                    break;
                case JX:
                    branch1 = (elevationChanges >= 2) && shadowBridge10;

                    if (branch1) {
                        level = ProjectLevel.ONE;
                    } else {
                        branch1 = (elevationChanges >= 0.5) && (elevationChanges <= 2) && shadowBridge10;
                        if (branch1) {
                            level = ProjectLevel.TWO;
                        } else {
                            level = ProjectLevel.THREE;
                        }
                    }
                    break;
                case XZ:
                    branch1 = (h >= 3) && aboveDig;

                    if (branch1) {
                        level = ProjectLevel.ONE;
                    } else {
                        branch1 = (h < 3) && aboveDig;
                        if (branch1) {
                            level = ProjectLevel.TWO;
                        } else {
                            level = ProjectLevel.THREE;
                        }
                    }
                    break;
                case ZJGC:
                    branch1 = pileDisFromSubWayStr <= 6;

                    if (branch1) {
                        level = ProjectLevel.ONE;
                    } else {
                        branch1 = (pileDisFromSubWayStr >= 6) && (pileDisFromSubWayStr <= 10);
                        if (branch1) {
                            level = ProjectLevel.TWO;
                        } else {
                            level = ProjectLevel.THREE;
                        }
                    }
                    break;
                case QTGC:
                    break;
            }
        }
        return level.getCode();
    }


    /**
     * 解析文本数据
     *
     * @param projectType
     * @param fileLink
     * @return
     */
    @SneakyThrows
    public HashMap<String, List<OutLineFileDataDTO>> getKVDataFromFile(Integer projectType, String fileLink) {
        HashMap<String, List<OutLineFileDataDTO>> data = Maps.newHashMap();

        File tempFile = File.createTempFile("stong_", ".docx");
        HttpUtil.downloadFile(fileLink, tempFile);

        try (XWPFDocument xwpfDocument = DocUtil.create(tempFile)) {
            //1 基坑工程
            //2 穿越工程
            //3加卸载、管线、桥梁、桩基及其他
            switch (projectType) {
                case 1:
                    List<XWPFTable> tables = xwpfDocument.getTables();
                    //地铁结构侧方

                    int index = 0;
                    for (int i = 0; i < tables.size(); i++) {
                        String text = tables.get(i).getRow(0).getCell(0).getText();
                        if ("地铁结构侧方类别".equals(text)) {
                            index = i;
                            break;
                        }
                    }
                    String[] fields = {"h", "dHorizontal", "s", "f"};

                    for (int i = 0; i < 4; i++) {
                        XWPFTable xwpfTable = tables.get(index + i);
                        if (i == 3) {
                            fields = new String[]{"h", "f", "aboveCoverWidth"};
                        }
                        String text1 = xwpfTable.getRow(0).getCell(1).getText();
                        List<OutLineFileDataDTO> objects = Lists.newArrayList();
                        for (int i1 = 0; i1 < (i == 0 ? 4 : 3); i1++) {
                            XWPFTableRow row = xwpfTable.getRow(2 + i1);
                            objects.add(OutLineFileDataDTO.builder().name(row.getCell(0).getText()).symbol(row.getCell(1).getText()).value(row.getCell(2).getText()).field(fields[i1]).note("").build());
                        }
                        data.put(text1, objects);

                    }
                    break;

                case 2:
                    tables = xwpfDocument.getTables();
                    index = 0;
                    for (int i = 0; i < tables.size(); i++) {
                        String text = tables.get(i).getRow(0).getCell(0).getText();
                        if ("地铁结构侧方类别".equals(text)) {
                            index = i;
                            break;
                        }
                    }
                    XWPFTable xwpfTable1 = tables.get(index);
                    String[] field = {"dVertical", "diameter", "isUnderCross", "dBridge", "parallelForward", "disTunnelGtDiameter"};
                    String text2 = xwpfTable1.getRow(0).getCell(1).getText();
                    List<OutLineFileDataDTO> objects1 = Lists.newArrayList();
                    for (int i = 0; i < 6; i++) {
                        XWPFTableRow row = xwpfTable1.getRow(2 + i);
                        objects1.add(OutLineFileDataDTO.builder().name(row.getCell(0).getText()).symbol(row.getCell(1).getText()).value(row.getCell(2).getText()).field(field[i]).note(row.getCell(3).getText()).build());
                    }
                    data.put(text2, objects1);
                    break;

                case 3:
                    String[][] keys = {
                            {"dVertical", "diameter", "isUnderCross", "dBridge"},
                            {"aboveDig", "h", "dHorizontal", "dVertical"},
                            {"elevationChanges", "dis30FromTunnel", "shadowBridge10", "aboveSimpleCover"}
                    };
                    tables = xwpfDocument.getTables();
                    int idx = 0;
                    for (int i = 0; i < tables.size(); i++) {
                        String text = tables.get(i).getRow(0).getCell(0).getText();
                        if ("1".equals(text)) {
                            idx = i;
                            break;
                        }
                    }
                    for (int i = 0; i < 5; i++) {
                        XWPFTable xwpfTable = tables.get(idx + i);
                        String text = xwpfTable.getRow(2).getCell(2).getText();
                        if (StrUtil.isNotBlank(text)) {
                            List<OutLineFileDataDTO> objects = Lists.newArrayList();
                            String text1 = xwpfTable.getRow(0).getCell(1).getText();
                            if (i < 3) {
                                for (int j = 0; j < keys[i].length; j++) {
                                    XWPFTableRow row = xwpfTable.getRow(2 + j);
                                    objects.add(OutLineFileDataDTO.builder().name(row.getCell(0).getText()).symbol(row.getCell(1).getText()).value(row.getCell(2).getText()).field(keys[i][j]).note(row.getCell(3).getText()).build());
                                }
                            } else if (i == 3) {
                                XWPFTableRow row = xwpfTable.getRow(2);
                                objects.add(OutLineFileDataDTO.builder().name(row.getCell(0).getText()).symbol(row.getCell(1).getText()).value(row.getCell(2).getText()).field("aboveDig").note(row.getCell(3).getText()).build());
                                row = xwpfTable.getRow(3);
                                objects.add(OutLineFileDataDTO.builder().name(row.getCell(0).getText()).symbol(row.getCell(1).getText()).value(row.getCell(2).getText()).field("h").note(row.getCell(3).getText()).build());

                            } else {
                                XWPFTableRow row = xwpfTable.getRow(2);
                                objects.add(OutLineFileDataDTO.builder().name(row.getCell(0).getText()).symbol(row.getCell(1).getText()).value(row.getCell(2).getText()).field("pileDisFromSubWayStr").note(row.getCell(3).getText()).build());

                            }
                            data.put(text1, objects);
                        }
                    }
                    break;
            }
        } catch (IOException e) {
            throw new BIZException("解析文件失败", e);
        } finally {
            FileUtil.del(tempFile);
        }
        return data;
    }


    /**
     * 生成监护大纲文件
     *
     * @param outlineProject
     * @return
     */
    @SneakyThrows
    public String genOutlineProjectFile(OutlineProject outlineProject) {

        File file = com.seari.custody.util.FileUtil.copyResourceFileToTemp("patternplate/1.docx");
        try (XWPFDocument xwpfDocument = DocUtil.create(file);
             ByteArrayOutputStream arrayOutputStream = new ByteArrayOutputStream(); BufferedOutputStream output = new BufferedOutputStream(arrayOutputStream);) {

            List<XWPFTable> tables = xwpfDocument.getTables();
            String projectBaseInfoByProjectId = projectService.getProjectBaseInfoByProjectId("05514d29-1e26-4d73-b67c-fef794f74f6a");

            List<String> split = Lists.newArrayList(Splitter.on("/").trimResults()
                    .omitEmptyStrings().split(projectBaseInfoByProjectId));


            String fileLink = minIoUtil.genShareLink(split.get(0), split.get(1));
            TechnicalAdvice technicalAdvice = new Common().finaled(fileLink);
            List<XWPFParagraph> paragraphs = xwpfDocument.getParagraphs();
            String format = numToUpper(DateUtil.thisYear()) + "年" + monthToUppder(DateUtil.thisMonth()) + "月";

            for (int i = 0; i < paragraphs.size(); i++) {
                //项目名称
                if (Objects.equals(paragraphs.get(i).getText().trim(), "{{projectName}}")) {
                    xwpfDocument.setParagraph(createParagraph(technicalAdvice.getProjectName() + "项目监护大纲", true, true, false, 36, "黑体"), i);
                    continue;
                }
                //文档建立日期
                if (Objects.equals(paragraphs.get(i).getText().trim(), "{{date}}")) {
                    xwpfDocument.setParagraph(createParagraph(format, true, true, false, 16, "黑体"), i);
                    continue;
                }
                //项目基础信息
                if (Objects.equals(paragraphs.get(i).getText().trim(), "{{projectBaseInfo}}")) {
                    xwpfDocument.setParagraph(createParagraphHasLineSpace("    " + Optional.ofNullable(technicalAdvice.getProjectOverview()).orElse("")), i);
                    continue;
                }
                //项目位置关系
                if (Objects.equals(paragraphs.get(i).getText().trim(), "{{relation}}")) {
                    xwpfDocument.setParagraph(createParagraphHasLineSpace("    " + Optional.ofNullable(technicalAdvice.getRelativeLocation()).orElse("")), i);
                    continue;
                }
                //地铁结构
                if (Objects.equals(paragraphs.get(i).getText().trim(), "{{struct}}")) {
                    xwpfDocument.setParagraph(createParagraphHasLineSpace("    " + Optional.ofNullable(technicalAdvice.getSubwayStructure()).orElse("")), i);
                    continue;
                }
                //监护等级
                if (Objects.equals(paragraphs.get(i).getText().trim(), "{{monitorObjectLevel}}")) {
                    xwpfDocument.setParagraph(createParagraphHasLineSpace("    本项目监护等级为" + ProjectLevel.getMsg(Integer.valueOf(outlineProject.getLevel()))), i);
                }
            }


            //监护大纲技术要求表
            XWPFTable xwpfTable = tables.get(1);

            String projectType = outlineProject.getProjectType();
            String s = String.format("%s基坑工程     %s穿越工程   %s加卸载、管线、桥梁、桩基及其他类   ", "1".equalsIgnoreCase(projectType) ? PICK : UN_PICK, "2".equalsIgnoreCase(projectType) ? PICK : UN_PICK, "3".equalsIgnoreCase(projectType) ? PICK : UN_PICK);
            xwpfTable.getRow(0).getCell(1).setParagraph(createParagraph(s));

            String lineType = outlineProject.getLineType();
            List<String> split1 = Lists.newArrayList(Splitter.on(",").split(lineType));

            s = String.format("        %s运营     %s在建      %s规划", split1.contains("1") ? PICK : UN_PICK, split1.contains("2") ? PICK : UN_PICK, split1.contains("3") ? PICK : UN_PICK);
            xwpfTable.getRow(1).getCell(2).setParagraph(createParagraph(s));

            String stationFlag = outlineProject.getStationFlag();
            List<String> split2 = Lists.newArrayList(Splitter.on(",").split(stationFlag));
            s = String.format("%s地上   %s地下", split2.contains("1") ? PICK : UN_PICK, split2.contains("2") ? PICK : UN_PICK);
            xwpfTable.getRow(2).getCell(2).setParagraph(createParagraph(s));
            XWPFTableCell cell1 = xwpfTable.getRow(2).getCell(2);
            Double height = Optional.ofNullable(outlineProject.getHeight()).orElse(0D);
            xwpfTable.getRow(2).getCell(4).setParagraph(createParagraph(String.valueOf(height)));

            String stationArea = outlineProject.getStationArea();
            XWPFTableCell cell2 = xwpfTable.getRow(3).getCell(2);
            List<String> split3 = Lists.newArrayList(Splitter.on(",").split(stationArea));
            s = String.format("%s单圆 %s双圆 %s矩形", split3.contains("1") ? PICK : UN_PICK, split3.contains("2") ? PICK : UN_PICK, split3.contains("3") ? PICK : UN_PICK);
            cell2.setParagraph(createParagraph(s));

            s = String.format("%s高架 %s地面 ", split3.contains("4") ? PICK : UN_PICK, split3.contains("5") ? PICK : UN_PICK);
            XWPFParagraph paragraph = createParagraph(s);
            cell2.getCTTc().getPList().add(paragraph.getCTP());
            cell2.addParagraph(paragraph);

            String level = outlineProject.getLevel();
            s = String.format("%s特级     %s一级    %s二级     %s三级", "1".equalsIgnoreCase(level) ? PICK : UN_PICK, "2".equalsIgnoreCase(level) ? PICK : UN_PICK, "3".equalsIgnoreCase(level) ? PICK : UN_PICK, "4".equalsIgnoreCase(level) ? PICK : UN_PICK);
            xwpfTable.getRow(4).getCell(1).setParagraph(createParagraph(s));

            xwpfTable.getRow(5).getCell(3).setParagraph(createParagraph(msg(outlineProject.getStationCzwy())));

            xwpfTable.getRow(6).getCell(3).setParagraph(createParagraph(msg(outlineProject.getStationMain())));

            xwpfTable.getRow(7).getCell(3).setParagraph(createParagraph(msg(outlineProject.getStationSub())));

            xwpfTable.getRow(8).getCell(3).setParagraph(createParagraph(msg(outlineProject.getDgCzwy())));

            xwpfTable.getRow(9).getCell(3).setParagraph(createParagraph(msg(outlineProject.getDgSpwy())));

            xwpfTable.getRow(10).getCell(3).setParagraph(createParagraph(msg(outlineProject.getDgSlbx())));

            xwpfTable.getRow(11).getCell(3).setParagraph(createParagraph(msg(outlineProject.getJxdCzwy())));

            xwpfTable.getRow(12).getCell(3).setParagraph(createParagraph(msg(outlineProject.getJxdSpwy())));

            xwpfTable.getRow(13).getCell(3).setParagraph(createParagraph(msg(outlineProject.getGjDccj())));

            xwpfTable.getRow(14).getCell(3).setParagraph(createParagraph(msg(outlineProject.getGjLzcj())));

            xwpfTable.getRow(15).getCell(3).setParagraph(createParagraph(msg(outlineProject.getGjLzqx())));

            xwpfTable.getRow(16).getCell(3).setParagraph(createParagraph(msg(outlineProject.getGjLzSpwy())));

            xwpfTable.getRow(17).getCell(3).setParagraph(createParagraph(msg(outlineProject.getGjTtcx())));

            xwpfTable.getRow(18).getCell(3).setParagraph(createParagraph(msg(outlineProject.getDmxCzwy())));

            xwpfTable.getRow(19).getCell(3).setParagraph(createParagraph(msg(outlineProject.getDmxSpwy())));


            s = String.format("%s深层土体沉降监测\n" +
                            "%s第三方复测\n" +
                            "%s远程监控\n" +
                            "%s加固抽检\n" +
                            "%s视频监控\n" +
                            "  技术咨询和复核验算（%s桩基/%s基坑/%s穿越）\n" +
                            "  场地标高测绘（%s绿化道路项目/%s基坑项目） \n" +
                            "%s边线复核测量（%s实测放样/%s利用资料放样）",
                    boolCheck(outlineProject.getSoilMonitor()), boolCheck(outlineProject.getThirdpartyMonitor()), boolCheck(outlineProject.getRemoteMonitor()), boolCheck(outlineProject.getFastenMonitor()),
                    boolCheck(outlineProject.getVideoMonitor()),
                    boolCheck(outlineProject.getPileCheck()),
                    boolCheck(outlineProject.getDitchCheck()),
                    boolCheck(outlineProject.getAcrossCheck()),
                    boolCheck(outlineProject.getGreenMeasure()),
                    boolCheck(outlineProject.getDitchMeasure()),
                    boolCheck(outlineProject.getSidelineFlag()),
                    boolCheck(outlineProject.getSidelineActFlag()),
                    boolCheck(outlineProject.getSidelineDataFlag())
            );
            XWPFTableCell cell = xwpfTable.getRow(20).getCell(1);
            for (String s1 : Splitter.on("\n").split(s)) {
                XWPFParagraph paragraph1 = createParagraph(s1);
                cell.getCTTc().getPList().add(paragraph1.getCTP());
                cell.addParagraph(paragraph1);
            }
            //备注
            xwpfTable.getRow(21).getCell(1).setParagraph(createParagraph(outlineProject.getNote()));


            String fileName = technicalAdvice.getProjectName() + "监护大纲.docx";
            xwpfDocument.write(output);
            output.flush();
            InputStreamSource inputStreamSource = new ByteArrayResource(arrayOutputStream.toByteArray());
            String extName = ".docx";
            MeetingFile meetingFile = new MeetingFile();
            meetingFile.setId(IdUtil.objectId());
            String path = IdUtil.objectId() + extName;
            minIoUtil.putObject(MinIoUtil.MEETING_BUCKET, path, inputStreamSource.getInputStream());
            meetingFile.setFileType(MeetingFileType.OUTLINE_PROJECT_WORD.getCode());
            meetingFile.setFilePath(path);
            meetingFile.setMeetingId(outlineProject.getProjectId());
            meetingFile.setFileExt(extName);
            meetingFile.setFileName(fileName);

            meetingFileService.uploadFile(meetingFile);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            FileUtil.del(file);
        }


//
//        cell.setText(s);


        return "";
    }


    /**
     * 生成报价清单
     */
    @SneakyThrows
    public InputStreamSource genPriceList(OutlineProject outlineProject) {
        ArrayList<String> titles = Lists.newArrayList("A 、", "B 、", "C 、", "D 、", "E 、", "F 、", "G 、", "H 、", "I 、", "J 、");
        int idx = 0;
        BigDecimal allPrice = new BigDecimal(0);
        File fileXlsxTemp = com.seari.custody.util.FileUtil.copyResourceFileToTemp("patternplate/price_template.xlsx");
        String fileLink = outLineProjectService.getOutlineProjectFile(outlineProject.getProjectId(), "admin", MeetingFileType.OUTLINE_PROJECT_WORD);
        File fileWord = File.createTempFile("stong_", ".docx");
        HttpUtil.downloadFile(fileLink, fileWord);

        try (ExcelWriter writer = ExcelUtil.getWriter(fileXlsxTemp); XWPFDocument xwpfDocument = DocUtil.create(fileWord); OutputStream outputStream = new FileOutputStream(fileXlsxTemp);
             ByteArrayOutputStream arrayOutputStream = new ByteArrayOutputStream(); BufferedOutputStream output = new BufferedOutputStream(arrayOutputStream);
        ) {
            CellStyle cellStyle = writer.getCellStyle();
            cellStyle.setWrapText(true);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);

            Sheet sheet = writer.getSheets().get(1);
            Sheet targetSheet = writer.getSheets().get(0);


            List<XWPFTable> tables = xwpfDocument.getTables();

            XWPFTable xwpfTable = tables.get(0);

            double month = Math.ceil(Double.parseDouble(xwpfTable.getRow(1).getCell(2).getText().trim()));
            BigDecimal jghlTotal = round(month * 3, 2);

            allPrice = add(allPrice, jghlTotal);

            String jhPrice = String.format("%s监护管理                            %s万", titles.get(idx++), jghlTotal);

            CellUtil.setCellValue(CellUtil.getCell(RowUtil.getOrCreateRow(sheet, 0), 0), jhPrice);
            Row row3 = RowUtil.getOrCreateRow(sheet, 3);
            CellUtil.setCellValue(CellUtil.getCell(row3, 5), month);
            CellUtil.setCellValue(CellUtil.getCell(row3, 8), jghlTotal);

            List<CellRangeAddressExcelVo> cellRangeAddressExcelVoList = Lists.newArrayList();
            cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(0, 0, 0, 8));
            cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(1, 2, 0, 0));
            cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(1, 2, 1, 4));
            cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(1, 2, 5, 5));
            cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(1, 2, 6, 6));
            cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(3, 3, 1, 4));
            POIUtil.copySheet(targetSheet, sheet, writer.getWorkbook(), writer.getWorkbook(), 0, cellRangeAddressExcelVoList);
            String level = numToUpper(Integer.parseInt(outlineProject.getLevel()) - 1) + "等";
            level = level.replace("○", "特");
            //获得人工监测 和自动化监测的表格
            List<String> names = Lists.newArrayList();
            List<String> points = Lists.newArrayList();
            List<String> numbers = Lists.newArrayList();

            Map<String, Integer> machineMap = Maps.newHashMap();


            xwpfDocument.getTables().forEach(f -> {
                int size = f.getRows().size();
                for (XWPFTableRow r : f.getRows()) {
                    String trim = r.getCell(0).getText().trim();
                    if (!Strings.isNullOrEmpty(trim)) {
                        if (Objects.equals("人工监测", trim)) {

                            XWPFTableRow row1 = f.getRow(0);
                            XWPFTableRow row2 = f.getRow(1);
                            XWPFTableRow lastRow = f.getRow(size - 1);
                            boolean flag = false;
                            for (XWPFTableCell cell : row1.getTableCells()) {
                                String text = cell.getText().trim();
                                if (flag) {
                                    names.add(text);
                                }
                                if ("监测内容".equals(text)) {
                                    flag = true;
                                }
                            }
                            flag = false;
                            for (XWPFTableCell cell : row2.getTableCells()) {
                                String text = cell.getText().trim();
                                if (flag) {
                                    points.add(text);
                                }
                                if ("监测点位数".equals(text)) {
                                    flag = true;
                                }
                            }
                            flag = false;
                            for (XWPFTableCell cell : lastRow.getTableCells()) {
                                String text = cell.getText().trim();
                                if (flag) {
                                    numbers.add(text);
                                }
                                if ("监测次数总计".equals(text)) {
                                    flag = true;
                                }
                            }

                            break;

                        } else if (Objects.equals("自动化监测", trim)) {

                            XWPFTableRow row1 = f.getRow(0);
                            XWPFTableRow row2 = f.getRow(1);
                            String name = row1.getCell(2).getText();
                            String value = row2.getCell(2).getText();
                            if (!Strings.isNullOrEmpty(value) && Integer.parseInt(value) != 0) {
                                machineMap.put(name, Integer.parseInt(value));
                            }
                            name = row1.getCell(3).getText();
                            value = row2.getCell(3).getText();
                            if (!Strings.isNullOrEmpty(value) && Integer.parseInt(value) != 0) {
                                machineMap.put(name, Integer.parseInt(value));
                            }
                            break;
                        }
                    }
                }
            });

            //人工监测
            if (!names.isEmpty()) {
                sheet = writer.getSheets().get(2);
                BigDecimal subTotal = new BigDecimal(0);
                //监护对象
                for (int i = 0; i < names.size(); i++) {
                    RowUtil.insertRow(sheet, 3 + i, 1);

                    String name = names.get(i);
                    double point = Integer.parseInt(points.get(i));
                    double number = Integer.parseInt(numbers.get(i));
                    Row row = RowUtil.getOrCreateRow(sheet, 3 + i);
                    row.setHeightInPoints(28.5F);
                    CellUtil.setCellValue(CellUtil.getCell(row, 0), 1 + i, cellStyle);
                    CellUtil.setCellValue(CellUtil.getCell(row, 2), name, cellStyle);

                    CellUtil.setCellValue(CellUtil.getCell(row, 3), level, cellStyle);
                    CellUtil.setCellValue(CellUtil.getCell(row, 4), "点.次", cellStyle);
                    CellUtil.setCellValue(CellUtil.getCell(row, 5), point, cellStyle);
                    CellUtil.setCellValue(CellUtil.getCell(row, 6), number, cellStyle);
                    CellUtil.setCellValue(CellUtil.getCell(row, 7), 50, cellStyle);
                    BigDecimal round1 = round(point * number * 50 / 10000, 2);
                    subTotal = NumberUtil.add(subTotal, round1);
                    CellUtil.setCellValue(CellUtil.getCell(row, 8), round1);
                }
                //小计
                Row orCreateRow = RowUtil.getOrCreateRow(sheet, 3 + names.size());
                CellUtil.setCellValue(CellUtil.getCell(orCreateRow, 0), 1 + names.size(), cellStyle);
                CellUtil.setCellValue(CellUtil.getCell(orCreateRow, 8), subTotal, cellStyle);
                //技术工作费
                BigDecimal jsgz = round(mul(subTotal, 0.22), 2);
                Row orCreateRow1 = RowUtil.getOrCreateRow(sheet, 4 + names.size());
                CellUtil.setCellValue(CellUtil.getCell(orCreateRow1, 0), 2 + names.size(), cellStyle);
                CellUtil.setCellValue(CellUtil.getCell(orCreateRow1, 8), jsgz, cellStyle);
                //合计
                BigDecimal hj = add(subTotal, jsgz);
                Row orCreateRow2 = RowUtil.getOrCreateRow(sheet, 5 + names.size());
                CellUtil.setCellValue(CellUtil.getCell(orCreateRow2, 0), 3 + names.size(), cellStyle);
                CellUtil.setCellValue(CellUtil.getCell(orCreateRow2, 8), hj, cellStyle);
                //总计
                allPrice = add(allPrice, hj);

                //人工监测汇总
                String rgjc = String.format("%s人工监测                           %s万", titles.get(idx++), hj);
                Row se = RowUtil.getOrCreateRow(sheet, 0);
                CellUtil.setCellValue(CellUtil.getCell(se, 0), rgjc);

                cellRangeAddressExcelVoList.clear();
                int lastRowNum = targetSheet.getPhysicalNumberOfRows() + 1;
                cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum, lastRowNum, 0, 8));
                for (int i = 0; i < 7; i++) {
                    cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + 1, lastRowNum + 2, i, i));
                }
                for (int i = 0; i < 3; i++) {
                    cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + 3 + names.size() + i, lastRowNum + 3 + names.size() + i, 1, 2));
                    cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + 3 + names.size() + i, lastRowNum + 3 + names.size() + i, 3, 7));
                }

                POIUtil.copySheet(targetSheet, sheet, writer.getWorkbook(), writer.getWorkbook(), lastRowNum, cellRangeAddressExcelVoList);
            }
            //自动化监测
            Set<String> strings = machineMap.keySet();

            for (String k : strings) {
                if (k.contains("激光测距仪")) {
                    sheet = writer.getSheets().get(3);
                    Integer jgyMount = machineMap.get(k);
                    BigDecimal jgcezSubTotal = new BigDecimal(0);
                    BigDecimal jgcezTotal = new BigDecimal(0);
                    for (Integer integer : Lists.newArrayList(3, 9, 10, 11)) {
                        Row xrow = RowUtil.getOrCreateRow(sheet, integer);

                        CellUtil.setCellValue(CellUtil.getCell(xrow, 3), jgyMount);
                        Double priceUnit = Double.valueOf(String.valueOf(CellUtil.getCellValue(CellUtil.getCell(xrow, 5))));
                        Double percent = Double.valueOf(String.valueOf(CellUtil.getCellValue(CellUtil.getCell(xrow, 7))));
                        BigDecimal sum = round(NumberUtil.mul(jgyMount, priceUnit, percent), 2);
                        CellUtil.setCellValue(CellUtil.getCell(xrow, 8), sum);
                    }
                    for (int i = 0; i < 11; i++) {
                        Double aDouble = Double.valueOf(String.valueOf(CellUtil.getCellValue(CellUtil.getCell(RowUtil.getOrCreateRow(sheet, 3 + i), 8))));
                        jgcezSubTotal = add(jgcezSubTotal, aDouble);
                    }
                    CellUtil.setCellValue(CellUtil.getCell(RowUtil.getOrCreateRow(sheet, 14), 8), jgcezSubTotal);
                    BigDecimal jgcjPrice = round(mul(jgcezSubTotal, 0.22), 2);
                    CellUtil.setCellValue(CellUtil.getCell(RowUtil.getOrCreateRow(sheet, 15), 8), jgcjPrice);
                    BigDecimal taxPrice = round(mul(add(jgcezSubTotal, jgcjPrice), 0.06), 2);

                    CellUtil.setCellValue(CellUtil.getCell(RowUtil.getOrCreateRow(sheet, 16), 8), taxPrice);
                    jgcezTotal = add(jgcjPrice, taxPrice, jgcezSubTotal);
                    CellUtil.setCellValue(CellUtil.getCell(RowUtil.getOrCreateRow(sheet, 17), 8), jgcezTotal);
                    allPrice = add(allPrice, jgcezTotal);
                    String jgcjy = String.format("%s自动化监测（激光测距仪）                           %s万", titles.get(idx++), jgcezTotal);
                    CellUtil.setCellValue(CellUtil.getCell(RowUtil.getOrCreateRow(sheet, 0), 0), jgcjy);


                    cellRangeAddressExcelVoList.clear();
                    System.out.println(targetSheet.getLastRowNum());
                    int lastRowNum = targetSheet.getPhysicalNumberOfRows() + 1;
                    cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum, lastRowNum, 0, 8));

                    cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + 1, lastRowNum + 2, 0, 0));
                    cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + 1, lastRowNum + 2, 1, 2));
                    cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + 1, lastRowNum + 2, 3, 3));
                    cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + 1, lastRowNum + 2, 4, 4));
                    cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + 1, lastRowNum + 1, 5, 6));
                    cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + 2, lastRowNum + 2, 5, 6));
                    cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + 1, lastRowNum + 2, 7, 7));

                    for (int i = 3; i < 14; i++) {
                        cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + i, lastRowNum + i, 1, 2));
                        cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + i, lastRowNum + i, 5, 6));
                    }
                    for (int i = 0; i < 4; i++) {
                        cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + 14 + i, lastRowNum + 14 + i, 1, 2));
                        cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + 14 + i, lastRowNum + 14 + i, 3, 7));
                    }

                    POIUtil.copySheet(targetSheet, sheet, writer.getWorkbook(), writer.getWorkbook(), lastRowNum, cellRangeAddressExcelVoList);
                } else if (k.contains("静力水准仪")) {
                    sheet = writer.getSheets().get(4);
                    Integer jlszMount = machineMap.get(k);
                    BigDecimal jlszSubTotal = new BigDecimal(0);
                    BigDecimal jlszTotal = new BigDecimal(0);
                    for (Integer integer : Lists.newArrayList(3, 7, 10, 11)) {
                        Row orCreateRow = RowUtil.getOrCreateRow(sheet, integer);

                        CellUtil.setCellValue(CellUtil.getCell(orCreateRow, 3), jlszMount);
                        Double priceUnit = Double.valueOf(String.valueOf(CellUtil.getCellValue(CellUtil.getCell(orCreateRow, 5))));
                        Double percent = Double.valueOf(String.valueOf(CellUtil.getCellValue(CellUtil.getCell(orCreateRow, 7))));
                        BigDecimal sum = round(NumberUtil.mul(jlszMount, priceUnit, percent), 2);
                        CellUtil.setCellValue(CellUtil.getCell(orCreateRow, 8), sum);
                    }
                    for (int i = 0; i < 11; i++) {
                        Double aDouble = Double.valueOf(String.valueOf(CellUtil.getCellValue(CellUtil.getCell(RowUtil.getOrCreateRow(sheet, 3 + i), 8))));
                        jlszSubTotal = add(jlszSubTotal, aDouble);
                    }
                    CellUtil.setCellValue(CellUtil.getCell(RowUtil.getOrCreateRow(sheet, 14), 8), jlszSubTotal);
                    BigDecimal jlszjPrice = round(mul(jlszSubTotal, 0.22), 2);
                    CellUtil.setCellValue(CellUtil.getCell(RowUtil.getOrCreateRow(sheet, 15), 8), jlszjPrice);
                    BigDecimal jlszTaxPrice = round(mul(add(jlszjPrice, jlszSubTotal), 0.06), 2);

                    CellUtil.setCellValue(CellUtil.getCell(RowUtil.getOrCreateRow(sheet, 16), 8), jlszTaxPrice);
                    jlszTotal = add(jlszjPrice, jlszTaxPrice, jlszSubTotal);
                    CellUtil.setCellValue(CellUtil.getCell(RowUtil.getOrCreateRow(sheet, 17), 8), jlszTotal);
                    allPrice = add(allPrice, jlszTotal);
                    String jlszy = String.format("%s自动化监测（静力水准仪                           %s万", titles.get(idx++), round(jlszTotal, 2));
                    CellUtil.setCellValue(CellUtil.getCell(RowUtil.getOrCreateRow(sheet, 0), 0), jlszy);


                    cellRangeAddressExcelVoList.clear();
                    int lastRowNum = targetSheet.getPhysicalNumberOfRows() + 1;
                    cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum, lastRowNum, 0, 8));

                    cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + 1, lastRowNum + 2, 0, 0));
                    cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + 1, lastRowNum + 2, 1, 2));
                    cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + 1, lastRowNum + 2, 3, 3));
                    cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + 1, lastRowNum + 2, 4, 4));
                    cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + 1, lastRowNum + 1, 5, 6));
                    cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + 2, lastRowNum + 2, 5, 6));
                    cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + 1, lastRowNum + 2, 7, 7));

                    for (int i = 3; i < 14; i++) {
                        cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + i, lastRowNum + i, 1, 2));
                        cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + i, lastRowNum + i, 5, 6));
                    }
                    for (int i = 0; i < 4; i++) {
                        cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + 14 + i, lastRowNum + 14 + i, 1, 2));
                        cellRangeAddressExcelVoList.add(new CellRangeAddressExcelVo(lastRowNum + 14 + i, lastRowNum + 14 + i, 3, 7));
                    }

                    POIUtil.copySheet(targetSheet, sheet, writer.getWorkbook(), writer.getWorkbook(), lastRowNum, cellRangeAddressExcelVoList);
                }

            }
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            String fastenMonitor = outlineProject.getFastenMonitor();
            if (Objects.equals("true", fastenMonitor)) {
                int physicalNumberOfRows = targetSheet.getLastRowNum() + 1;
                Row orCreateRow = RowUtil.getOrCreateRow(targetSheet, physicalNumberOfRows);
                String jgcj = String.format("%s加固抽检：                            20.00              万元", titles.get(idx++));
                allPrice = add(allPrice, 20);

                orCreateRow.createCell(0, CellType.STRING);
                CellUtil.setCellValue(CellUtil.getCell(orCreateRow, 0), jgcj, cellStyle);
                for (int i = 1; i < 9; i++) {
                    orCreateRow.createCell(i, CellType.STRING);
                    CellUtil.setCellValue(CellUtil.getCell(orCreateRow, i), "", cellStyle);
                }
                targetSheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(physicalNumberOfRows, physicalNumberOfRows, 0, 8));
            }

            String sidelineFlag = outlineProject.getSidelineFlag();
            if (Objects.equals("true", sidelineFlag)) {
                int physicalNumberOfRows = targetSheet.getPhysicalNumberOfRows() + 1;
                Row orCreateRow = RowUtil.getOrCreateRow(targetSheet, physicalNumberOfRows);
                BigDecimal v = round(Optional.ofNullable(outlineProject.getSidelineActNum()).orElse(0.0) + Optional.ofNullable(outlineProject.getSidelineDataNum()).orElse(0.0), 2);
                BigDecimal round = round(div(mul(v, 90.06), pow(10, 4)), 2);
                allPrice = add(allPrice, round);
                String bxfh = String.format("%s边线复核：                            %s              万元            %s(米)*90.06(元/米)", titles.get(idx++), round, v);
                orCreateRow.createCell(0, CellType.STRING);

                Cell cell = CellUtil.getCell(orCreateRow, 0);
                orCreateRow.createCell(0, CellType.STRING);

                CellUtil.setCellValue(cell, bxfh, cellStyle);
                for (int i = 1; i < 9; i++) {
                    orCreateRow.createCell(i, CellType.STRING);
                    CellUtil.setCellValue(CellUtil.getCell(orCreateRow, i), "", cellStyle);
                }
                targetSheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(physicalNumberOfRows, physicalNumberOfRows, 0, 8));

            }

            BigDecimal v1 = round(Optional.ofNullable(outlineProject.getDitchMeasureNum()).orElse(0.0) + Optional.ofNullable(outlineProject.getGreenMeasureNum()).orElse(0.0), 2);
            if (v1.compareTo(BigDecimal.ZERO) != 0) {
                BigDecimal round2 = round(div(mul(v1, 1.92), pow(10, 4)), 2);
                int physicalNumberOfRows = targetSheet.getPhysicalNumberOfRows() + 1;
                Row orCreateRow = RowUtil.getOrCreateRow(targetSheet, physicalNumberOfRows);
                orCreateRow.createCell(0, CellType.STRING);

                allPrice = add(allPrice, round2);
                String bgfc = String.format("%s标高复测：                           %s              万元            %s(平方米)*1.92(元/平方米)", titles.get(idx++), round2, v1);
                Cell cell = CellUtil.getCell(orCreateRow, 0);

                CellUtil.setCellValue(cell, bgfc, cellStyle);
                for (int i = 1; i < 9; i++) {
                    orCreateRow.createCell(i, CellType.STRING);
                    CellUtil.setCellValue(CellUtil.getCell(orCreateRow, i), "", cellStyle);
                }
                targetSheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(physicalNumberOfRows, physicalNumberOfRows, 0, 8));

            }

            String laserSweepFlag = outlineProject.getLaserSweepFlag();
            if (Objects.equals("true", laserSweepFlag)) {
                int physicalNumberOfRows = targetSheet.getPhysicalNumberOfRows() + 1;
                Row orCreateRow = RowUtil.getOrCreateRow(targetSheet, physicalNumberOfRows);
                orCreateRow.createCell(0, CellType.STRING);

                Integer laserSweepNum = Optional.ofNullable(outlineProject.getLaserSweepNum()).orElse(0);

                BigDecimal v2 = round(laserSweepNum * 1.5, 2);
                allPrice = add(allPrice, v2);
                String swjg = String.format("%s三维激光扫描：                 %s             万元             %s次", titles.get(idx++), v2, laserSweepNum);
                Cell cell = CellUtil.getCell(orCreateRow, 0);

                CellUtil.setCellValue(cell, swjg, cellStyle);
                for (int i = 1; i < 9; i++) {
                    orCreateRow.createCell(i, CellType.STRING);
                    CellUtil.setCellValue(CellUtil.getCell(orCreateRow, i), "", cellStyle);
                }
                targetSheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(physicalNumberOfRows, physicalNumberOfRows, 0, 8));

            }
            int physicalNumberOfRows = targetSheet.getPhysicalNumberOfRows() + 1;
            Row orCreateRow = RowUtil.getOrCreateRow(targetSheet, physicalNumberOfRows);
            orCreateRow.createCell(0, CellType.STRING);


            String zj = String.format("%s总价：                                       %s              万元", titles.get(idx++), round(allPrice, 2));
            Cell cell = CellUtil.getCell(orCreateRow, 0);

            CellUtil.setCellValue(cell, zj, cellStyle);
            for (int i = 1; i < 9; i++) {
                orCreateRow.createCell(i, CellType.STRING);
                CellUtil.setCellValue(CellUtil.getCell(orCreateRow, i), "", cellStyle);
            }
            targetSheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(physicalNumberOfRows, physicalNumberOfRows, 0, 8));

            for (int i = 0; i < 4; i++) {
                writer.getWorkbook().removeSheetAt(1);
            }

            RowUtil.removeRow(RowUtil.getOrCreateRow(targetSheet, 4));


            writer.flush(output);
            output.flush();
            return new ByteArrayResource(arrayOutputStream.toByteArray());

        } catch (Exception e) {
            throw new BIZException(e);
        } finally {
            FileUtil.del(fileXlsxTemp);
            FileUtil.del(fileWord);
        }
    }

    public static XWPFParagraph newParagraph(XWPFParagraph paragraph) {
        XmlCursor xmlCursor = paragraph.getCTP().newCursor();  //从段落中获取光标
        xmlCursor.toNextSibling();                          //将光标移动到下一个段落      ，因为光标生成是在段落的最开始位置 所以需要向下移动一个Xml的光标，也就是将光标移动到下一个段落之前，
        paragraph = paragraph.getDocument().insertNewParagraph(xmlCursor);//在光标所在位置添加新的段落
        return paragraph;
    }


    /**
     * 人工 自动
     *
     * @param value
     * @return
     */
    private String msg(Integer value) {
        value = Optional.ofNullable(value).orElse(0);
        return String.format("%s自动   %s人工", (1 == value || 3 == value) ? PICK : UN_PICK, (2 == value || 3 == value) ? PICK : UN_PICK);
    }

    /**
     * true or false
     *
     * @param value
     * @return
     */
    private String boolCheck(String value) {
        value = Optional.ofNullable(value).orElse("false");
        return "true".equalsIgnoreCase(value) ? PICK : UN_PICK;
    }

    public static XWPFParagraph createParagraph(String s) {

        XWPFDocument document = new XWPFDocument();
        XWPFParagraph paragraph = document.createParagraph();


        XWPFRun run = paragraph.createRun();
        run.setFontFamily("宋体");
        run.setFontSize(12);

        run.setText(s);

        return paragraph;
    }

    public static XWPFParagraph createParagraphHasLineSpace(String s) {
        XWPFParagraph paragraph = createParagraph(s);
        setSingleLineSpacing(paragraph);
        return paragraph;
    }

    public static XWPFParagraph createParagraphCenter(String s) {
        return createParagraph(s, true, false, false, 16, "宋体");
    }

    public static XWPFParagraph createParagraph(String s, Boolean center, Boolean bold, Boolean right, Integer fontSize, String fontFamily) {
        XWPFDocument document = new XWPFDocument();
        XWPFParagraph paragraph = document.createParagraph();
        if (center) {
            paragraph.setAlignment(ParagraphAlignment.CENTER);
        }
        if (right) {
            paragraph.setAlignment(ParagraphAlignment.RIGHT);
        }
        if (StrUtil.isNotBlank(s) && s.contains("\n")) {
            String[] split = s.split("\n");
            for (int i = 0; i < split.length; i++) {
                XWPFRun run = paragraph.createRun();
                run.setFontFamily("宋体");
                run.setFontSize(12);
                run.setText(split[i].trim());
                run.addBreak();
            }
        } else {
            XWPFRun run = paragraph.createRun();
            run.setFontFamily(fontFamily);
            run.setFontSize(fontSize);
            if (bold) {
                run.setBold(true);
            }
            run.setText(s);

        }
        setSingleLineSpacing(paragraph);
        return paragraph;
    }

    /**
     * 行距
     *
     * @param para
     */
    public static void setSingleLineSpacing(XWPFParagraph para) {
        CTPPr ppr = para.getCTP().getPPr();
        if (ppr == null) {
            ppr = para.getCTP().addNewPPr();
        }
        CTSpacing spacing = ppr.isSetSpacing() ? ppr.getSpacing() : ppr.addNewSpacing();
        spacing.setAfter(BigInteger.valueOf(0));
        spacing.setBefore(BigInteger.valueOf(0));
        spacing.setLineRule(STLineSpacingRule.AUTO);
        spacing.setLine(BigInteger.valueOf(350));
    }

    // 将数字转化为大写（字体格式可自己定义）
    public static String numToUpper(int num) {
//        String u[] = {"○", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖"};
        String u[] = {"○", "一", "二", "三", "四", "五", "六", "七", "八", "九"};
        char[] str = String.valueOf(num).toCharArray();
        String rstr = "";
        for (int i = 0; i < str.length; i++) {
            rstr = rstr + u[Integer.parseInt(str[i] + "")];
        }
        return rstr;
    }

    // 月转化为大写
    public static String monthToUppder(int month) {
        if (month < 10) {
            return numToUpper(month);
        } else if (month == 10) {
            return "十";
        } else {
            return "十" + numToUpper(month - 10);
        }
    }


    public static void main(String[] args) {
        File fileXlsxTemp = com.seari.custody.util.FileUtil.copyResourceFileToTemp("patternplate/price_list.xlsx");
        File fileWord = com.seari.custody.util.FileUtil.copyResourceFileToTemp("patternplate/jhdg_case.docx");
        ExcelWriter writer = ExcelUtil.getWriter(new File("C:\\Users\\lx\\AppData\\Local\\Temp\\stong_7238124335316018386.xlsx"));


        Sheet sheet = writer.getSheets().get(0);
        int lastRowNum = sheet.getLastRowNum();

//        sheet.shiftRows(30,lastRowNum,-10);


        for (CellRangeAddress mergedRegion : sheet.getMergedRegions()) {

            System.out.println(mergedRegion.formatAsString());

        }


//        try (XWPFDocument xwpfDocument = DocUtil.create(fileWord);
//        ) {
//
//            //获得人工监测 和自动化监测的表格
//            ArrayList<String> names = Lists.newArrayList();
//            ArrayList<String> points = Lists.newArrayList();
//            ArrayList<String> numbers = Lists.newArrayList();
//            xwpfDocument.getTables().forEach(f -> {
//                int size = f.getRows().size();
//                f.getRows().forEach(r -> {
//                    String trim = r.getCell(0).getText().trim();
//
//
//                    if (!Strings.isNullOrEmpty(trim)) {
//                        if (Objects.equals("人工监测", trim)) {
//                            XWPFTableRow row1 = f.getRow(0);
//                            XWPFTableRow row2 = f.getRow(1);
//                            XWPFTableRow lastRow = f.getRow(size - 1);
//                            boolean flag = false;
//                            for (XWPFTableCell cell : row1.getTableCells()) {
//                                String text = cell.getText().trim();
//                                if (flag) {
//                                    names.add(text);
//                                }
//                                if ("监测内容".equals(text)) {
//                                    flag = true;
//                                }
//                            }
//                            flag = false;
//                            for (XWPFTableCell cell : row2.getTableCells()) {
//                                String text = cell.getText().trim();
//                                if (flag) {
//                                    points.add(text);
//                                }
//                                if ("监测点位数".equals(text)) {
//                                    flag = true;
//                                }
//                            }
//                            flag = false;
//                            for (XWPFTableCell cell : lastRow.getTableCells()) {
//                                String text = cell.getText().trim();
//                                if (flag) {
//                                    numbers.add(text);
//                                }
//                                if ("监测次数总计".equals(text)) {
//                                    flag = true;
//                                }
//                            }
//
//                        } else if (Objects.equals("自动化监测", trim)) {
//
//                        }
//                    }
//                });
//
//            });
//            System.out.println("ddd");
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//        String url = "http://172.20.41.71:9000/projectword/%E5%B7%A5%E7%A8%8B%E4%BF%A1%E6%81%AF%E6%A6%82%E5%86%B5558b93db-f141-473f-9108-fcdc8f0a91c8.docx?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=minioadmin%2F20210831%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20210831T085324Z&X-Amz-Expires=7200&X-Amz-SignedHeaders=host&X-Amz-Signature=1971f8c29ec1d8a29ff59230a2a58f1a797e3e1f291b2f636847435e90921155";
//        URL resources = ResourceUtil.getResource("patternplate/1.docx");

        OutlineProjectCalc outlineProjectCalc = new OutlineProjectCalc();
//        OutputStream outputStream = outlineProjectCalc.genPriceList();


//        if (path.endsWith(".doc")) {
//            try (FileInputStream is = new FileInputStream(file);
//                 WordExtractor ex = new WordExtractor(is); XWPFDocument xwpfDocument = new XWPFDocument(is)) {
//
//                List<XWPFParagraph> paragraphs = xwpfDocument.getParagraphs();
//
//                for (
//                        int i = 0; i < paragraphs.size(); i++) {
//
//                    System.out.println(i);
//                    System.out.println(paragraphs.get(i).getText());
//
//                }
//
//            } catch (Exception ioException) {
//                ioException.printStackTrace();
//            }
//
//        } else if (path.endsWith("docx")) {
//            XWPFDocument xwpfDocument = DocUtil.create(file);
//            List<XWPFParagraph> paragraphs = xwpfDocument.getParagraphs();
//
//            for (
//                    int i = 0; i < paragraphs.size(); i++) {
//
//                System.out.println(i);
//                System.out.println(paragraphs.get(i).getText());
//
//            }
//
//        }

//        XWPFDocument xwpfDocument = new Word07Writer((FileUtil.file(resources))).getDoc();
//
//        List<XWPFParagraph> paragraphs = xwpfDocument.getParagraphs();
//
//        for (
//                int i = 0; i < paragraphs.size(); i++) {
//
//            System.out.println(i);
//            System.out.println(paragraphs.get(i).getText());
//
//        }


//        List<XWPFTable> tables = xwpfDocument.getTables();
//        XWPFTable xwpfTable = tables.get(1);
//        XWPFTableCell cell = xwpfTable.getRow(20).getCell(1);
//        List<XWPFParagraph> paragraphs = cell.getParagraphs();
//        List<XWPFTable> tables1 = cell.getTables();
//
//        for (XWPFParagraph paragraph : paragraphs) {
//
//            for (XWPFRun run : paragraph.getRuns()) {
//
//                String text = run.text();
//                run.addCarriageReturn();
//                System.out.println(text);
//                System.out.println("888888");
//            }
//        }
//        OutlineProjectCalc outlineProjectCalcUtil = new OutlineProjectCalc();
//        OutlineProject outlineProject = outlineProjectCalcUtil.getOutlineProject(1, "201907022");
//        String s = outlineProjectCalcUtil.genOutlineProjectFile(outlineProject);

//        File file = null;
//        try {
//
//            file = FileUtil.file(new URI("http://172.20.41.71:9000/test/%E5%B7%A5%E7%A8%8B%E4%BF%A1%E6%81%AF%E6%A6%82%E5%86%B5-%E3%80%90%E8%A1%8C%E6%94%BF%E8%AE%B8%E5%8F%AF%E3%80%91%E5%9F%BA%E5%9D%91%E7%B1%BB-%E6%B7%BB%E5%8A%A0%E5%A4%A7%E7%BA%B2%E6%95%B0%E6%8D%AE.docx?Content-Disposition=attachment%3B%20filename%3D%22%E5%B7%A5%E7%A8%8B%E4%BF%A1%E6%81%AF%E6%A6%82%E5%86%B5-%E3%80%90%E8%A1%8C%E6%94%BF%E8%AE%B8%E5%8F%AF%E3%80%91%E5%9F%BA%E5%9D%91%E7%B1%BB-%E6%B7%BB%E5%8A%A0%E5%A4%A7%E7%BA%B2%E6%95%B0%E6%8D%AE.docx%22&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=minioadmin%2F20210528%2F%2Fs3%2Faws4_request&X-Amz-Date=20210528T074655Z&X-Amz-Expires=432000&X-Amz-SignedHeaders=host&X-Amz-Signature=d8671bee4678d853aa5f8012c3c623f84fec9cc1055be5af483997ced5682f0a"));
//        } catch (URISyntaxException e) {
//            e.printStackTrace();
//        }
//
//
//        System.out.println(file.getName());
//        int i = 0;
//        for (; i < 4; i++) {
//            System.out.println(i);
//
//        }
//        System.out.println(i);

    }
}





